﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using UCNKompetenceBors.Model;

namespace UCNKompetenceBors.Database
{
    public class dbExchange
    {
        /// <summary>
        /// Gets a list of all categories.
        /// </summary>
        /// <returns>Returns a list of Category objects.</returns>
        public static List<Category> GetAllCategories()
        {
            try
            {
                List<Category> categories = new List<Category>();

                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select cat.categoryID, cat.categoryName, loc.categoryLocalizedName " +
                                   "from Category cat, CategoryLocalized loc " +
                                   "where loc.categoryLocale = 'en-GB' "+
                                   "and loc.categoryID = cat.categoryID " +
                                   "order by categoryName asc";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Category cat = new Category();
                        cat.CategoryID = Convert.ToInt32(dr["categoryID"]);
                        cat.CategoryName = dr["categoryName"].ToString();
                        cat.LocalizedCategoryName = dr["categoryLocalizedName"].ToString();
                        categories.Add(cat);
                    }
                }

                return categories;
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Updates a category, based on the input parameters.
        /// </summary>
        /// <param name="categoryID">Specifies the ID of the category to be updated.</param>
        /// <param name="categoryName">Specifies the category name to be used in the update.</param>
        /// <param name="localizedCategoryName">Specifies the localized category name to be used in the update.</param>
        public static void UpdateCategory(int categoryId, string categoryName, string localizedCategoryName)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "update Category " +
                                   "set categoryName = @categoryName " +
                                   "where categoryID = @categoryID ";

                    query += "update CategoryLocalized " +
                             "set categoryLocalizedName = @categoryLocalizedName " +
                             "where categoryID = @categoryID";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@categoryName", categoryName);
                    cmd.Parameters.AddWithValue("@categoryID", categoryId);
                    cmd.Parameters.AddWithValue("@categoryLocalizedName", localizedCategoryName);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Deletes a category, based on the categoryId.
        /// </summary>
        /// <param name="categoryId">Specifies the ID of the category to be deleted.</param>
        public static void DeleteCategory(int categoryId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "begin transaction " +
                                   "declare @rowsDeleted  int " +
                                   "delete from CategoryLocalized " +
                                   "where categoryID = @categoryID " +
                                   "delete from Category " +
                                   "where categoryID = @categoryID " +
                                   "set @rowsDeleted = @@ROWCOUNT " +
                                   "if @@ERROR <> 0 or @rowsDeleted = 0 " +
                                   "begin " +
                                   "rollback transaction " +
                                   "end " +
                                   "if @@TRANCOUNT > 0 " +
                                   "commit transaction";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@categoryID", categoryId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Creates a category, based on the input parameters.
        /// </summary>
        /// <param name="categoryName">Specifies the name of the category to be created.</param>
        /// <param name="localizedCategoryName">Specifies the localized name of the category to be created.</param>
        public static void CreateCategory(string categoryName, string localizedCategoryName)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "insert into Category (categoryName)"
                    + "values (@categoryName)";

                    query += " insert into CategoryLocalized (categoryLocale, categoryLocalizedName)"
                    + "values (@categoryLocale, @categoryLocalizedName)";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@categoryName", categoryName);
                    cmd.Parameters.AddWithValue("@categoryLocale", "en-GB");
                    cmd.Parameters.AddWithValue("@categoryLocalizedName", localizedCategoryName);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Validates whether or not the specified category name already exists in the database.
        /// </summary>
        /// <param name="categoryName">Specifies the name of the category to be validated.</param>
        /// <returns>Returns a bool specifying whether or not the specified category name already exists in the database.</returns>
        public static bool ValidateCategoryName(string categoryName)
        {
            List<SqlParameter> paramList = new List<SqlParameter>()
            {
                new SqlParameter()
                {
                    ParameterName = "@categoryName",
                    Value = categoryName
                }
            };

            return dbGeneral.ExecuteStoredProc("sprocValidateCategoryName", paramList);
        }

        /// <summary>
        /// Validates whether or not the specified localized category name already exists in the database.
        /// </summary>
        /// <param name="localizedCategoryName">Specifies the localized name of the category to be validated.</param>
        /// <returns>Returns a bool specifying whether or not the specified localized category name already exists in the database.</returns>
        public static bool ValidateLocalizedCategoryName(string localizedCategoryName)
        {
            List<SqlParameter> paramList = new List<SqlParameter>()
            {
                new SqlParameter()
                {
                    ParameterName = "@categoryName",
                    Value = localizedCategoryName
                }
            };

            return dbGeneral.ExecuteStoredProc("sprocValidateCategoryLocalizedName", paramList);
        }

        /// <summary>
        /// Validates whether or not the specified categoryId is in use.
        /// </summary>
        /// <param name="categoryId">Specifies the ID of the category to be validated.</param>
        /// <returns>Returns a bool specifying whether or not the specified categoryId is in use.</returns>
        public static bool ValidateCategoryUsed(int categoryId)
        {
            List<SqlParameter> paramList = new List<SqlParameter>()
            {
                new SqlParameter()
                {
                    ParameterName = "@categoryID",
                    Value = categoryId
                }
            };

            return dbGeneral.ExecuteStoredProc("sprocValidateCategoryUsed", paramList);
        }

        /// <summary>
        /// Gets a list of all purchases, based on input parameters.
        /// </summary>
        /// <param name="timespan">Specifies which timespan the results should be in.</param>
        /// <param name="categoryId">Specifies which category the results should be in.</param>
        /// <param name="search">Specifies a string to search for in the title and description of the results.</param>
        /// <returns>Returns a list of Purchase objects</returns>
        public static List<Purchase> GetAllPurchases(int timespan, int categoryId = 0, string search = null)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<Purchase> purchases = new List<Purchase>();

                    string query = "select pur.purchaseID, pur.purchaseTitle, pur.purchaseDescription, pur.purchaseCreatedDate, pur.purchaseUserID, pur.purchaseIsActive, pur.purchaseCategoryID, cat.categoryName, catLoc.categoryLocalizedName " +
                                   "from Purchase pur, Category cat, CategoryLocalized catLoc " +
                                   "where pur.purchaseIsActive = 1";

                    SqlCommand cmd = new SqlCommand();

                    if (categoryId != 0)
                    {
                        query += " AND pur.purchaseCategoryID = @categoryId";
                        cmd.Parameters.AddWithValue("@categoryId", categoryId);
                    }

                    if (timespan != 0)
                    {
                        query += " AND pur.purchaseCreatedDate >= CURRENT_TIMESTAMP - @timeSpan";
                        cmd.Parameters.AddWithValue("@timeSpan", timespan);
                    }

                    if (search != null)
                    {
                        query += " AND (pur.purchaseTitle like '%' + @searchValue + '%' or pur.purchaseDescription like '%' + @searchValue + '%')";
                        cmd.Parameters.AddWithValue("@searchValue", search);
                    }

                    query += " AND cat.categoryID = pur.purchaseCategoryID" +
                             " AND catLoc.categoryID = cat.categoryID" +
                             " ORDER BY pur.purchaseCreatedDate Desc";

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Purchase purchase = new Purchase(Convert.ToInt32(dr["purchaseID"]));

                        purchase.Title = dr["purchaseTitle"].ToString();
                        purchase.Description = dr["purchaseDescription"].ToString();
                        purchase.CreatedDate = Convert.ToDateTime(dr["purchaseCreatedDate"]);
                        purchase.User = dbLogin.GetUserById(Guid.Parse(dr["purchaseUserID"].ToString()));
                        purchase.Category = new Category(Convert.ToInt32(dr["purchaseCategoryID"]), dr["categoryName"].ToString());
                        purchase.Category.LocalizedCategoryName = dr["categoryLocalizedName"].ToString();
                        purchase.IsActive = Convert.ToBoolean(dr["purchaseIsActive"]);

                        purchases.Add(purchase);
                    }

                    con.Close();
                    con.Dispose();

                    return purchases;
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of all sales, based on input parameters.
        /// </summary>
        /// <param name="timespan">Specifies which timespan the results should be in.</param>
        /// <param name="categoryId">Specifies which category the results should be in.</param>
        /// <param name="search">Specifies a string to search for in the title and description of the results.</param>
        /// <returns>Returns a list of Sale objects</returns>
        public static List<Sale> GetAllSales(int timespan, int categoryId = 0, string search = null)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<Sale> sales = new List<Sale>();

                    string query = "select sal.saleID, sal.saleTitle, sal.saleDescription, sal.saleCreatedDate, sal.saleUserID, sal.saleIsActive, sal.saleCategoryID, cat.categoryName, catLoc.categoryLocalizedName " +
                                   "from Sale sal, Category cat, CategoryLocalized catLoc " +
                                   "where sal.saleIsActive = 1";

                    SqlCommand cmd = new SqlCommand();

                    if (categoryId != 0)
                    {
                        query += " AND sal.saleCategoryID = @categoryId";
                        cmd.Parameters.AddWithValue("@categoryId", categoryId);
                    }

                    if (timespan != 0)
                    {
                        query += " AND sal.saleCreatedDate >= CURRENT_TIMESTAMP - @timeSpan";
                        cmd.Parameters.AddWithValue("@timeSpan", timespan);
                    }

                    if (search != null)
                    {
                        query += " AND (sal.saleTitle like '%' + @searchValue + '%' or sal.saleDescription like '%' + @searchValue + '%')";
                        cmd.Parameters.AddWithValue("@searchValue", search);
                    }

                    query += " AND cat.categoryID = sal.saleCategoryID" +
                             " AND catLoc.categoryId = cat.CategoryID" +
                             " ORDER BY sal.saleCreatedDate Desc";

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Sale sale = new Sale(Convert.ToInt32(dr["saleID"]));

                        sale.Title = dr["saleTitle"].ToString();
                        sale.Description = dr["saleDescription"].ToString();
                        sale.CreatedDate = Convert.ToDateTime(dr["saleCreatedDate"]);
                        sale.User = dbLogin.GetUserById(Guid.Parse(dr["saleUserID"].ToString()));
                        sale.Category = new Category(Convert.ToInt32(dr["saleCategoryID"]), dr["categoryName"].ToString());
                        sale.Category.LocalizedCategoryName = dr["categoryLocalizedName"].ToString();
                        sale.IsActive = Convert.ToBoolean(dr["saleIsActive"]);

                        sales.Add(sale);
                    }

                    con.Close();
                    con.Dispose();

                    return sales;
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a purchase, based on the purchaseId parameter.
        /// </summary>
        /// <param name="purchaseId">Specifies the ID of the purchase to be retrieved</param>
        /// <returns>Returns a Purchase object</returns>
        public static Purchase GetPurchaseDetails(int purchaseId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    Purchase purchase = new Purchase(purchaseId);

                    string query = "select pur.purchaseTitle, pur.purchaseDescription, pur.purchaseCreatedDate, pur.purchaseUserID, pur.purchaseCategoryID, cat.categoryName, loc.categoryLocalizedName " +
                                   "from Purchase pur, Category cat, CategoryLocalized loc " +
                                   "where pur.purchaseIsActive = 1 " +
                                   "AND pur.purchaseID = @purchaseID " +
                                   "AND cat.categoryID = pur.purchaseCategoryID " +
                                   "AND loc.categoryID = cat.categoryID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@purchaseID", purchaseId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        purchase.Title = dr["purchaseTitle"].ToString();
                        purchase.Description = dr["purchaseDescription"].ToString();

                        Category cat = new Category(Convert.ToInt32(dr["purchaseCategoryID"]), dr["categoryName"].ToString());
                        cat.LocalizedCategoryName = dr["categoryLocalizedName"].ToString();

                        purchase.Category = cat;

                        purchase.CreatedDate = Convert.ToDateTime(dr["purchaseCreatedDate"]);
                        purchase.User = dbLogin.GetUserById(Guid.Parse(dr["purchaseUserID"].ToString()));
                    }

                    con.Close();
                    con.Dispose();

                    return purchase;
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a sale, based on the saleId parameter.
        /// </summary>
        /// <param name="saleId">Specifies the ID of the sale to be retrieved</param>
        /// <returns>Returns a Sale object</returns>
        public static Sale GetSaleDetails(int saleId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    Sale sale = new Sale(saleId);

                    string query = "select sal.saleTitle, sal.saleDescription, sal.saleCreatedDate, sal.saleUserID, sal.saleCategoryID, cat.categoryName, loc.categoryLocalizedName " +
                                   "from Sale sal, Category cat, CategoryLocalized loc " +
                                   "where sal.saleIsActive = 1 " +
                                   "AND sal.saleID = @saleID " +
                                   "AND cat.categoryID = sal.saleCategoryID " +
                                   "AND loc.categoryID = cat.categoryID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@saleID", saleId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        sale.Title = dr["saleTitle"].ToString();
                        sale.Description = dr["saleDescription"].ToString();
                        sale.Category = new Category(Convert.ToInt32(dr["saleCategoryID"]), dr["categoryName"].ToString());
                        sale.CreatedDate = Convert.ToDateTime(dr["saleCreatedDate"]);
                        sale.User = dbLogin.GetUserById(Guid.Parse(dr["saleUserID"].ToString()));
                    }

                    con.Close();
                    con.Dispose();

                    return sale;
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Creates a purchase, based on the input parameters.
        /// </summary>
        /// <param name="title">Specifies the title to be used.</param>
        /// <param name="description">Specifies the description to be used.</param>
        /// <param name="category">Specifies the ID of the category to be used.</param>
        public static void CreatePurchase(string title, string description, int categoryId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "insert into Purchase (purchaseTitle, purchaseDescription, purchaseCategoryID, purchaseCreatedDate, purchaseUserID, purchaseIsActive)"
                    + "values (@purchaseTitle, @purchaseDescription, @categoryId, CURRENT_TIMESTAMP, @purchaseUserID, 1)";
                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    MembershipUser mu = Membership.GetUser();
                    cmd.Parameters.AddWithValue("@purchaseTitle", title);

                    description = description.Replace(Environment.NewLine, "<br />");

                    cmd.Parameters.AddWithValue("@purchaseDescription", description);

                    cmd.Parameters.AddWithValue("@categoryId", categoryId);

                    cmd.Parameters.AddWithValue("@purchaseUserID", mu.ProviderUserKey);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Creates a sale, based on the input parameters.
        /// </summary>
        /// <param name="title">Specifies the title to be used.</param>
        /// <param name="description">Specifies the description to be used.</param>
        /// <param name="categoryId">Specifies the category to be used.</param>
        public static void CreateSale(string title, string description, int categoryId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "insert into Sale (saleTitle, saleDescription, saleCategoryID, saleCreatedDate, saleUserID, saleIsActive)"
                    + "values (@saleTitle, @saleDescription, @categoryId, CURRENT_TIMESTAMP, @saleUserID, 1)";
                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    MembershipUser mu = Membership.GetUser();
                    cmd.Parameters.AddWithValue("@saleTitle", title);

                    description = description.Replace(Environment.NewLine, "<br />");

                    cmd.Parameters.AddWithValue("@saleDescription", description);

                    cmd.Parameters.AddWithValue("@categoryId", categoryId);

                    cmd.Parameters.AddWithValue("@saleUserID", mu.ProviderUserKey);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of purchases, based on the amount parameter.
        /// </summary>
        /// <param name="amount">Specifies the amount of purchases to be retrieved.</param>
        /// <returns>Returns a list of Purchase objects</returns>
        public static List<Purchase> GetRecentPurchases(int amount)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<Purchase> purchases = new List<Purchase>();

                    string query = "select top " + amount + "purchaseID, purchaseTitle " +
                                   "from Purchase " +
                                   "order by purchaseCreatedDate desc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Purchase purchase = new Purchase(Convert.ToInt32(dr["purchaseID"]));

                        purchase.Title = dr["purchaseTitle"].ToString();

                        purchases.Add(purchase);
                    }

                    con.Close();
                    con.Dispose();

                    return purchases;
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of sales, based on the amount parameter.
        /// </summary>
        /// <param name="amount">Specifies the amount of sales to be retrieved.</param>
        /// <returns>Returns a list of Sale objects</returns>
        public static List<Sale> GetRecentSales(int amount)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<Sale> sales = new List<Sale>();

                    string query = "select top " + amount + "saleID, saleTitle " +
                                   "from Sale " +
                                   "order by saleCreatedDate desc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Sale sale = new Sale(Convert.ToInt32(dr["saleID"]));

                        sale.Title = dr["saleTitle"].ToString();

                        sales.Add(sale);
                    }

                    con.Close();
                    con.Dispose();

                    return sales;
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of purchases, based on the userId parameter.
        /// </summary>
        /// <param name="userId">Specifies the ID of the user to which the purchases belongs to.</param>
        /// <returns>Returns a list of Purchase objects</returns>
        public static List<Purchase> GetPurchasesByUserId(Guid userId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<Purchase> purchases = new List<Purchase>();

                    string query = "select pur.purchaseID, pur.purchaseTitle, pur.purchaseCreatedDate, pur.purchaseIsActive, pur.purchaseCategoryID, cat.categoryName, catLoc.categoryLocalizedName " +
                                   "from Purchase pur, Category cat, CategoryLocalized catLoc " +
                                   "where pur.purchaseUserID = @userID " +
                                   "AND cat.categoryID = pur.purchaseCategoryID " +
                                   "AND catLoc.categoryID = cat.categoryID " +
                                   "order by pur.purchaseCreatedDate desc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@userID", userId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Purchase purchase = new Purchase(Convert.ToInt32(dr["purchaseID"]));

                        purchase.Title = dr["purchaseTitle"].ToString();
                        purchase.CreatedDate = Convert.ToDateTime(dr["purchaseCreatedDate"]);
                        purchase.User = dbLogin.GetUserById(userId);
                        purchase.Category = new Category(Convert.ToInt32(dr["purchaseCategoryID"]), dr["categoryName"].ToString());
                        purchase.Category.LocalizedCategoryName = dr["categoryLocalizedName"].ToString();
                        purchase.IsActive = Convert.ToBoolean(dr["purchaseIsActive"]);

                        purchases.Add(purchase);
                    }

                    con.Close();
                    con.Dispose();

                    return purchases;
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of sales, based on the userId parameter.
        /// </summary>
        /// <param name="userId">Specifies the ID of the user to which the sales belongs to.</param>
        /// <returns>Returns a list of Sale objects</returns>
        public static List<Sale> GetSalesByUserId(Guid userId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<Sale> sales = new List<Sale>();

                    string query = "select sal.saleID, sal.saleTitle, sal.saleCreatedDate, sal.saleIsActive, sal.saleCategoryID, cat.categoryName, catLoc.categoryLocalizedName " +
                                   "from Sale sal, Category cat, CategoryLocalized catLoc " +
                                   "where sal.saleUserID = @userID " +
                                   "AND cat.categoryID = sal.saleCategoryID " +
                                   "AND catLoc.categoryID = cat.categoryID " +
                                   "order by sal.saleCreatedDate desc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@userID", userId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Sale sale = new Sale(Convert.ToInt32(dr["saleID"]));

                        sale.Title = dr["saleTitle"].ToString();
                        sale.CreatedDate = Convert.ToDateTime(dr["saleCreatedDate"]);
                        sale.User = dbLogin.GetUserById(userId);
                        sale.Category = new Category(Convert.ToInt32(dr["saleCategoryID"]), dr["categoryName"].ToString());
                        sale.Category.LocalizedCategoryName = dr["categoryLocalizedName"].ToString();
                        sale.IsActive = Convert.ToBoolean(dr["saleIsActive"]);

                        sales.Add(sale);
                    }

                    con.Close();
                    con.Dispose();

                    return sales;
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Deletes a purchase, based on the purchaseId parameter.
        /// </summary>
        /// <param name="purchaseId">Specifies the ID of the purchase to be deleted.</param>
        public static void DeletePurchase(int purchaseId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "delete from Purchase " +
                                   "where purchaseID = @purchaseID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@purchaseID", purchaseId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Deletes a sale, based on the saleId parameter.
        /// </summary>
        /// <param name="saleId">Specifies the ID of the sale to be deleted.</param>
        public static void DeleteSale(int saleId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "delete from Sale " +
                                   "where saleID = @saleID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@saleID", saleId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Updates a purchase, based on the purchaseId parameter.
        /// </summary>
        /// <param name="title">Specifies the title to be used.</param>
        /// <param name="description">Specifies the description to be used.</param>
        /// <param name="categoryId">Specifies the ID of the category to be used.</param>
        /// <param name="isActive">Specifies whether or not the purchase is active.</param>
        /// <param name="purchaseId">Specifies the ID of the purchase to be updated.</param>
        public static void UpdatePurchase(string title, string description, int categoryId, bool isActive, int purchaseId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "update Purchase " +
                                   "set purchaseTitle = @purchaseTitle, purchaseDescription = @purchaseDescription, purchaseCategoryID = @categoryId, purchaseIsActive = @purchaseIsActive " +
                                   "where purchaseID = @purchaseID";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@purchaseTitle", title);

                    description = description.Replace(Environment.NewLine, "<br />");

                    cmd.Parameters.AddWithValue("@purchaseDescription", description);

                    cmd.Parameters.AddWithValue("@categoryId", categoryId);

                    cmd.Parameters.AddWithValue("@purchaseIsActive", (isActive) ? 1 : 0);

                    cmd.Parameters.AddWithValue("@purchaseID", purchaseId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }

        /// <summary>
        /// Updates a sale, based on the saleId parameter.
        /// </summary>
        /// <param name="title">Specifies the title to be used.</param>
        /// <param name="description">Specifies the description to be used.</param>
        /// <param name="categoryId">Specifies the ID of the category to be used.</param>
        /// <param name="isActive">Specifies whether or not the sale is active.</param>
        /// <param name="saleId">Specifies the ID of the sale to be updated.</param>
        public static void UpdateSale(string title, string description, int categoryId, bool isActive, int saleId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "update Sale " +
                                   "set saleTitle = @saleTitle, saleDescription = @saleDescription, saleCategoryID = @categoryId, saleIsActive = @saleIsActive " +
                                   "where saleID = @saleID";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@saleTitle", title);

                    description = description.Replace(Environment.NewLine, "<br />");

                    cmd.Parameters.AddWithValue("@saleDescription", description);

                    cmd.Parameters.AddWithValue("@categoryId", categoryId);

                    cmd.Parameters.AddWithValue("@saleIsActive", (isActive) ? 1 : 0);

                    cmd.Parameters.AddWithValue("@saleID", saleId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //Insert logging here
                throw;
            }
            catch (Exception e)
            {
                //Insert logging here
                throw;
            }
        }
    }
}